# -*- coding: utf-8 -*-
import json,time,sqlite3,MySQLdb,telnetlib,threading
import pingthread
from Queue import Queue

# 把列表转成sql的where in语句中的()部分
def c2sql(x):
  if len(x)==1:
    return str(tuple(x))[:-2]+")"
  else:
    return str(tuple(x))
    
# 从mysql的rnd表取ndip数据
def read_mysql():
  nds=[]
  conn=MySQLdb.connect(host="172.16.100.31",user="root",passwd="admin",db="rainbow_3_0")
  cursor=conn.cursor()
  # cursor.execute("select dscfgnwipaddr from rnd order by dscfgnwipaddr desc limit 10")
  cursor.execute("select dscfgnwipaddr from rnd order by dscfgnwipaddr")
  [nds.append(x[0]) for x in cursor.fetchall()]
  cursor.close()
  conn.close()
  print len(nds)
  return nds

# 检查是否有nd增加或删除
def check_sqlite3(nds):
  ips=[]
  conn=sqlite3.connect(r'lkt.db3')
  conn.text_factory = str
  [ips.append(x[0]) for x in conn.execute("select ndip from rnd order by ndip").fetchall()]
  sql="delete from rnd where ndip in "+c2sql(set(ips)-set(nds))
  conn.execute(sql)
  for x in tuple(set(nds)-set(ips)):
    sql="insert into rnd(ndip) values('"+x+"')"
    conn.execute(sql)
  conn.commit()
  conn.close()
  
def do_write_sqlite3(nds):
  s=time.time()
  last_change_time=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
  conn=sqlite3.connect(r'lkt.db3')
  conn.text_factory = str
  ICMP_notalive=[]
  Telnet_notalive=[]
  [ICMP_notalive.append(x[0]) for x in conn.execute("select ndip from rnd where bICMP=0").fetchall()]
  [Telnet_notalive.append(x[0]) for x in conn.execute("select ndip from rnd where bTelnet=0").fetchall()]
  icmp_alive=list(set(ICMP_notalive)-set(lICMP))
  telnet_alive=list(set(Telnet_notalive)-set(lTelnet))
  union_alive=list(set(icmp_alive)|set(telnet_alive))
  print "icmp notalive:",len(lICMP)
  print "icmp alive:",icmp_alive
  print "telnet notalive:",len(lTelnet)
  print "telnet alive:",telnet_alive
  
  icmp_notalive=list(set(lICMP)-set(ICMP_notalive))
  telnet_notalive=list(set(lTelnet)-set(Telnet_notalive))
  union_notalive=list(set(icmp_notalive)|set(telnet_notalive))
  
  and_notalive=list(set(lICMP)&set(lTelnet))
  xor_notalive=list(set(lICMP)^set(lTelnet))
  print "and_notalive:",len(and_notalive)
  print "xor_notalive:",xor_notalive
  sql="update rnd set iICMP=iICMP+1,iTelnet=iTelnet+1,dtPoll='"+last_change_time+"'"
  conn.execute(sql)
  
  for x in union_alive:
    if (x in icmp_alive) and (x in telnet_alive):
      sql="update rnd set bAlive=1,bICMP=1,bTelnet=1,iICMP=1,iTelnet=1,dtICMP='"+last_change_time+"',dtTelnet='"+last_change_time+"' where ndip='"+x+"'"
    else:
      if x in icmp_alive:
        if conn.execute("select ndip from rnd where bTelnet=1 and ndip='"+x+"'").fetchone():
          sql="update rnd set bAlive=1,bICMP=1,iICMP=1,dtICMP='"+last_change_time+"' where ndip='"+x+"'"
        else:
          sql="update rnd set bICMP=1,iICMP=1,dtICMP='"+last_change_time+"' where ndip='"+x+"'"
      if x in telnet_alive:
        if conn.execute("select ndip from rnd where bICMP=1 and ndip='"+x+"'").fetchone():
          sql="update rnd set bAlive=1,bTelnet=1,iTelnet=1,dtTelnet='"+last_change_time+"' where ndip='"+x+"'"
        else:
          sql="update rnd set bTelnet=1,iTelnet=1,dtTelnet='"+last_change_time+"' where ndip='"+x+"'"
    conn.execute(sql)
    
  for x in union_notalive:
    if x in icmp_notalive:
      sql="update rnd set bAlive=0,bICMP=0,iICMP=1,dtICMP='"+last_change_time+"' where ndip='"+x+"'"
    if x in telnet_notalive:
      sql="update rnd set bAlive=0,bTelnet=0,iTelnet=1,dtTelnet='"+last_change_time+"' where ndip='"+x+"'"
    conn.execute(sql)
  
  conn.commit()
  conn.close()
  print "write_sqlite3:",time.time()-s


def write_sqlite3(nds):
  write_success=False
  while not write_success:
    try:
      do_write_sqlite3(nds)
      write_success=True
    except sqlite3.OperationalError,e:
      time.sleep(1)


  
#具体的处理函数，负责处理单个任务
def do_somthing_using(arguments):
  # bAlive=pingthread.do_one(arguments,1,64)
  # if bAlive==None:
    # lICMP.append(arguments)
  lost,mrtt,lrtt=pingthread.quiet_ping(arguments,1,2)
  if lost==100:
    lICMP.append(arguments)
  try:
    tn.open(arguments,23,1)
  except:
    lTelnet.append(arguments)
  
#这个是工作进程，负责不断从队列取数据并处理
def working():
  while True:
    arguments = q.get()
    do_somthing_using(arguments)
    q.task_done()
    
def Test_Poll(nds):
  s=time.time()
  #把JOBS排入队列
  for i in nds:
    q.put(i)
  #等待所有JOBS完成
  q.join()
  print time.time()-s
  
q = Queue()
#fork NUM个线程等待队列
NUM = 40
for i in range(NUM):
  t = threading.Thread(target=working)
  t.setDaemon(True)
  t.start()

tn = telnetlib.Telnet()
nds=read_mysql()
start=time.time()
while True:
  lICMP=[]
  lTelnet=[]
  delaytime=time.time()
  config = json.load(open("config.txt",'r'))
  if time.time()-start>config["MySQL_Poll_Time"]*3600:
    nds=read_mysql()
    start=time.time()
  check_sqlite3(nds)
  Test_Poll(nds)
  print "threading:",threading.activeCount()
  write_sqlite3(nds)
  delaytime=config["Test_Poll_Time"]-(time.time()-delaytime)
  if delaytime<=0:
    delaytime=config["Test_Poll_Time"]
  print "sleep icmp",delaytime
  time.sleep(delaytime)
